package by.grsu.transport.persistence.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import by.grsu.transport.persistence.dao.UserDao;
import by.grsu.transport.persistence.entity.UserEntity;

public class UserDaoImpl implements UserDao {

	private static final String GET_ALL_USERS_QUERY = "select id, first_name, last_name, role_id, login, password, registration_date, is_locked from user";
	private static final String GET_USER_QUERY = "select id, first_name, last_name, role_id, login, password, registration_date, is_locked from user where login=?";
	private static final String GET_USERS_FOR_ROLE_QUERY = "select id, first_name, last_name, role_id, login, password, registration_date, is_locked from user where role_id=?";
	private static final String INSERT_USER_QUERY = "INSERT INTO USER (first_name, last_name, role_id, login, password, registration_date, is_locked) VALUE (?, ?, 3, ?, ?, now(), false)";
	private static final Logger logger = Logger.getLogger(UserDaoImpl.class);

	private Connection connection;

	public UserDaoImpl(Connection conn) {
		connection = conn;
		if (connection == null) {
			throw new NullPointerException("Invalid connection!");
		}
	}

	@Override
	public UserEntity getUser(String login) {

		PreparedStatement stmt = null;
		ResultSet rs = null;
		UserEntity userEntity = null;

		try {

			stmt = connection.prepareStatement(UserDaoImpl.GET_USER_QUERY);
			stmt.setString(1, login);
			rs = stmt.executeQuery();
			rs = getUniqueResult(rs);
			if (rs.next()) {
				userEntity = new UserEntity();
				userEntity.setId(rs.getInt("id"));
				userEntity.setFirstName(rs.getString("first_name"));
				userEntity.setLastName(rs.getString("last_name"));
				userEntity.setRole(rs.getInt("role_id"));
				userEntity.setLogin(rs.getString("login"));
				userEntity.setPassword(rs.getString("password"));
				userEntity.setRegistrationDate(rs.getDate("registration_date"));
				userEntity.setIsLocked(rs.getBoolean("is_locked"));
			}
		} catch (Exception e) {
			logger.warn("Problem with getting data from Data Base: " + e.getMessage());
		} finally {
			closeStatment(rs, stmt);
		}
		return userEntity;
	}

	private ResultSet getUniqueResult(ResultSet rs) throws SQLException {

		rs.last();
		if (rs.getRow() > 1) {
			logger.error("You have some problems with user's logins. There are two or more the same logins.");
			throw new RuntimeException("Login Exception!");
		}
		rs.beforeFirst();
		if (rs.next()) {
			rs.beforeFirst();
			return rs;
		} else {
			return null;
		}
	}

	@Override
	public List<UserEntity> getUsers(int role) {

		List<UserEntity> users = new ArrayList<UserEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {

			stmt = connection
					.prepareStatement(UserDaoImpl.GET_USERS_FOR_ROLE_QUERY);
			stmt.setInt(1, role);
			rs = stmt.executeQuery();

			while (rs.next()) {
				UserEntity newUser = new UserEntity();
				newUser.setId(rs.getInt("id"));
				newUser.setFirstName(rs.getString("first_name"));
				newUser.setLastName(rs.getString("last_name"));
				newUser.setRole(rs.getInt("role_id"));
				newUser.setLogin(rs.getString("login"));
				newUser.setPassword(rs.getString("password"));
				newUser.setRegistrationDate(rs.getDate("registration_date"));
				newUser.setIsLocked(rs.getBoolean("is_locked"));
				users.add(newUser);
			}
		} catch (Exception e) {
			logger.warn("Problem with getting data from Data Base: "
					+ e.getMessage());
		} finally {
			closeStatment(rs, stmt);
		}
		return users;

	}

	private void closeStatment(ResultSet rs, PreparedStatement stmt) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception ignore) {
				logger.warn("ResultSet can't be closed!");
			}
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (Exception ignore) {
				logger.warn("PreparedStatement can't be closed!");
			}
		}
	}

	@Override
	public Class<UserEntity> getEntityClass() {
		return UserEntity.class;
	}

	@Override
	public UserEntity findById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<UserEntity> findAll() {
		List<UserEntity> users = new ArrayList<UserEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {

			stmt = connection.prepareStatement(UserDaoImpl.GET_ALL_USERS_QUERY);
			rs = stmt.executeQuery();

			while (rs.next()) {
				UserEntity newUser = new UserEntity();
				newUser.setId(rs.getInt("id"));
				newUser.setFirstName(rs.getString("first_name"));
				newUser.setLastName(rs.getString("last_name"));
				newUser.setRole(rs.getInt("role_id"));
				newUser.setLogin(rs.getString("login"));
				newUser.setPassword(rs.getString("password"));
				newUser.setRegistrationDate(rs.getDate("registration_date"));
				newUser.setIsLocked(rs.getBoolean("is_locked"));
				users.add(newUser);
			}
		} catch (Exception e) {
			logger.error("Problem with getting data from Data Base: "
					+ e.getMessage());
		} finally {
			closeStatment(rs, stmt);
		}
		return users;

	}


	@Override
	public void insert(UserEntity entity) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(UserDaoImpl.INSERT_USER_QUERY);
			stmt.setString(1, entity.getFirstName());
			stmt.setString(2, entity.getLastName());
			stmt.setString(3, entity.getLogin());
			stmt.setString(4, entity.getPassword());
			stmt.executeUpdate();

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
					logger.warn("PreparedStatement can't be closed!");
				}
			}
		} catch (SQLException e) {
			logger.error("Can't create new user!");
		}
	}

	@Override
	public void update(UserEntity entity) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(UserEntity entity) {
		// TODO Auto-generated method stub

	}

}
